﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Arc.DairyMan.DairyMan.Model;
using Arc.WinForms.Controls;

namespace Arc.DairyMan.DairyMan.DataAccess
{
    public class dalMilkCollection : AbsDal
    {
        public override int Transaction()
        {
            try
            {
                int Result = 0;
                SqlCommand cmd = Connection.CreateCommand();
                MilkCollection _MilkCollection = (MilkCollection)ModelObj;
                switch (_MilkCollection.TransactionMode)
                {
                    case "I":
                        #region Insert
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = _MilkCollection.FriendlyName + "_" + _MilkCollection.TransactionMode;
                        //_Sabhasad.PrimaryKey = Convert.ToInt32(cmd.ExecuteScalar());
                        //if (Convert.ToInt32(_ModelObj.PrimaryKey) > 0) 

                        cmd.Parameters.Add(new SqlParameter("@SabhasadId", _MilkCollection.SabhasadId));
                        cmd.Parameters.Add(new SqlParameter("@Date", _MilkCollection.Date));
                        cmd.Parameters.Add(new SqlParameter("@Shift", _MilkCollection.Shift));
                        cmd.Parameters.Add(new SqlParameter("@MilkType", _MilkCollection.MilkType));
                        cmd.Parameters.Add(new SqlParameter("@Quantity", _MilkCollection.Quantity));
                        cmd.Parameters.Add(new SqlParameter("@Fat", _MilkCollection.Fat));
                        cmd.Parameters.Add(new SqlParameter("@Rate", _MilkCollection.Rate));
                        cmd.Parameters.Add(new SqlParameter("@Amount", _MilkCollection.Amount));

                        Result = ExecuteCommand(cmd);

                        break;
                        #endregion
                    case "U":
                        #region Update
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = _MilkCollection.FriendlyName + "_" + _MilkCollection.TransactionMode;
                        //_Sabhasad.PrimaryKey = Convert.ToInt32(cmd.ExecuteScalar());
                        //if (Convert.ToInt32(_ModelObj.PrimaryKey) > 0) 

                        cmd.Parameters.Add(new SqlParameter("@SabhasadId", _MilkCollection.SabhasadId));
                        cmd.Parameters.Add(new SqlParameter("@Date", _MilkCollection.Date));
                        cmd.Parameters.Add(new SqlParameter("@Shift", _MilkCollection.Shift));
                        cmd.Parameters.Add(new SqlParameter("@MilkType", _MilkCollection.MilkType));
                        cmd.Parameters.Add(new SqlParameter("@Quantity", _MilkCollection.Quantity));
                        cmd.Parameters.Add(new SqlParameter("@Fat", _MilkCollection.Fat));
                        cmd.Parameters.Add(new SqlParameter("@Rate", _MilkCollection.Rate));
                        cmd.Parameters.Add(new SqlParameter("@Amount", _MilkCollection.Amount));
                        cmd.Parameters.Add(new SqlParameter("@MilkCollectionId", _MilkCollection.MilkCollectionId));


                        Result = ExecuteCommand(cmd); ;

                        break;
                        #endregion
                    case "D":
                        #region Delete
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = _MilkCollection.FriendlyName + "_" + _MilkCollection.TransactionMode;

                        cmd.Parameters.Add(new SqlParameter("@MilkCollectionId", _MilkCollection.PrimaryKey));

                        Result = ExecuteCommand(cmd); ;

                        break;
                        #endregion
                    default:
                        throw new Exception("Invalid Transaction Mode:" + _MilkCollection.TransactionMode);

                }
                return Result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public override void Select(string Critiera, DataSet Ds)
        {
            try
            {
                string _SQL = @"Select * from " + ModelObj.FriendlyName + "_Select";
                if (Critiera.Trim() != string.Empty)
                    _SQL += " Where " + Critiera;
                GetDataTable(_SQL, ModelObj.FriendlyName, Ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public override void Search(string Critiera, DataSet Ds)
        {
            try
            {
                string _SQL = @"Select * from " + ModelObj.FriendlyName + "_Search";
                if (Critiera.Trim() != "")
                    _SQL += " Where " + Critiera;
                GetDataTable(_SQL, ModelObj.FriendlyName, Ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public DataTable GetShiftReport(DateTime CollectionDate, int Shift)
        {
            string _SQL = @"Select 
                s.Code,
                s.Name,
                mc.Quantity Liter,
                mc.Fat,
                convert(varchar, mc.Rate) Rate,
                mc.Amount 
                from " + ModelObj.FriendlyName + @" mc 
                join Sabhasad s on mc.SabhasadId = s.SabhasadId 
                where Convert(varchar,mc.Date,112) = '" + CollectionDate.ToyyyyMMdd_112() + @"' and 
                mc.Shift = " + Shift + @" union all
                Select 
                '',
                'Total',
                Sum(mc.Quantity),
                avg(mc.Fat),
                '',
                sum(mc.Amount)
                from " + ModelObj.FriendlyName + @" mc 
                join Sabhasad s on mc.SabhasadId = s.SabhasadId 
                where Convert(varchar,mc.Date,112) = '" + CollectionDate.ToyyyyMMdd_112() + @"' and 
                mc.Shift = " + Shift;

            DataTable _dtNextCode = base.GetDataTable(_SQL, "ShiftReport", new DataSet());

            return _dtNextCode;
        }

        public DataTable GetPaymentReport(DateTime FromDate, DateTime ToDate)
        {
            string _SQL = @"Select 
                max(s.Code) Code,
                max(s.Name) Name,
                sum(mc.Quantity) Liter,
                avg(mc.Fat) [Avg Fat],
                sum(mc.Amount) [Total Amount],
                count(s.SabhasadId) [Total Days]
                from milkcollection mc 
                join Sabhasad s on mc.SabhasadId = s.SabhasadId 
                where Convert(varchar,mc.Date,112) >= '" + FromDate.ToyyyyMMdd_112() + @"' and
                Convert(varchar,mc.Date,112) <= '" + ToDate.ToyyyyMMdd_112() + @"'  
                group by mc.SabhasadId
            union all
            Select 
	            '',
	            'Total',
	            sum(mc.Quantity) Liter,
	            avg(mc.Fat) Fat,
	            sum(mc.Amount) [Total Amount],
	            count(s.SabhasadId) [Total Days]
	            from milkcollection mc 
	            join Sabhasad s on mc.SabhasadId = s.SabhasadId
                where Convert(varchar,mc.Date,112) >= '" + FromDate.ToyyyyMMdd_112() + @"' and
                Convert(varchar,mc.Date,112) <= '" + ToDate.ToyyyyMMdd_112() + @"'  
            ";

            DataTable _dtNextCode = base.GetDataTable(_SQL, "PaymentReport", new DataSet());

            return _dtNextCode;
        }
    }
}
